package br.com.rotaestatistica.daos;

import java.sql.PreparedStatement;
import java.sql.ResultSet;

import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import br.com.rotaestatistica.entities.EnderecoEntity;
import br.com.rotaestatistica.utils.FabricaDeConexao;

public class EnderecoDAO extends FactoryDAO {
	public List<EnderecoEntity> selectAllEnderecosAsList() {
		List<EnderecoEntity> listaRetorno = new ArrayList<EnderecoEntity>();
		ResultSet rs = null;
		Statement stm = null;
		try{
			stm = FabricaDeConexao.getConexao().createStatement();
		
			String sql = "SELECT * FROM Endereco";
		
			rs = stm.executeQuery(sql);
		
			while (rs.next()){
				EnderecoEntity end = new EnderecoEntity();
				end.setId_Endereco(rs.getLong ("idEndereco"));
				end.setRua(rs.getString ("rua"));
				end.setCep(rs.getLong("cep"));
				end.setBairro(rs.getString ("bairro"));
				end.setCidade(rs.getString ("cidade"));
				end.setUf(rs.getString ("uf"));
				
				listaRetorno.add(end);
			}
		}
		catch (SQLException e){
			e.printStackTrace();
		}
		finally{
			try{
				rs.close();
				stm.close();
				FabricaDeConexao.fechaConexao();
			}
			catch (SQLException e){
				e.printStackTrace();
			}
		}
		return listaRetorno;
	}

	public EnderecoEntity selectEnderecoById(long idEndereco) {
		EnderecoEntity end = new EnderecoEntity(); 
		ResultSet rs = null;
		PreparedStatement pstm = null;
		
		String sql = "SELECT * FROM Endereco where idEndereco =?";
		
		try{
			pstm = FabricaDeConexao.getConexao().prepareStatement(sql);
			pstm.setLong(1, idEndereco);			
		
			rs = pstm.executeQuery(sql);
			
			while (rs.next()){
				end.setId_Endereco(rs.getLong ("idEndereco"));
				end.setRua(rs.getString ("rua"));
				end.setCep(rs.getLong("cep"));
				end.setBairro(rs.getString ("bairro"));
				end.setCidade(rs.getString ("cidade"));
				end.setUf(rs.getString ("uf"));
			}
		}
		catch (SQLException e){
			e.printStackTrace();
		}
		finally{
			try{
				rs.close();
				pstm.close();
				FabricaDeConexao.fechaConexao();
			}
			catch (SQLException e){
				e.printStackTrace();
			}
		}
		return end;
	}

	public boolean insertEndereco(EnderecoEntity endereco) {
		PreparedStatement pstm = null;
		
		String sql = "INSERT INTO Endereco (rua, cep, bairro, cidade, uf) VALUES ('?', ?, '?', '?', '?')";
		
		try{			
			pstm = FabricaDeConexao.getConexao().prepareStatement(sql);
			
			pstm.setString(1, endereco.getRua());
			pstm.setLong(2, endereco.getCep());
			pstm.setString(3, endereco.getBairro());
			pstm.setString(4, endereco.getCidade());
			pstm.setString(5, endereco.getUf());
			
			pstm.executeUpdate(sql);
			return true;
		}
		catch (SQLException e){
			e.printStackTrace();
			return false;
		}
		finally{
			try{
				pstm.close();
				FabricaDeConexao.fechaConexao();
			}
			catch (SQLException e){
				e.printStackTrace();
			}
		}
		
	}

	public boolean deleteEnderecoById(long idEndereco) {
		PreparedStatement pstm = null;
		
		String sql = "DELETE FROM Endereco where id_Endereco =?";
		
		try{
			pstm = FabricaDeConexao.getConexao().prepareStatement(sql);
			pstm.setLong(1, idEndereco);
					
			pstm.executeUpdate(sql);
			
			return true;
		}
		catch (SQLException e){
			e.printStackTrace();
			return false;
		}
		finally{
			try{
				pstm.close();
				FabricaDeConexao.fechaConexao();
			}
			catch (SQLException e){
				e.printStackTrace();
			}
		}

	}

	public boolean updateEndereco(EnderecoEntity endereco) {
		PreparedStatement pstm = null;
		
		String sql = "UPDATE Endereco SET rua =?, cep =?, bairro =?, cidade =?, uf =? WHERE idEndereco =?";
		try{

			pstm = FabricaDeConexao.getConexao().prepareStatement(sql);

			pstm.setString(1, endereco.getRua());
			pstm.setLong(2, endereco.getCep());
			pstm.setString(3, endereco.getBairro());
			pstm.setString(4, endereco.getCidade());
			pstm.setString(5, endereco.getUf());
			pstm.setLong(6, endereco.getId_Endereco());
						
			pstm.executeUpdate();
			return true;
		}
		catch (SQLException e){
			e.printStackTrace();
			return false;
		}
		finally{
			try{
				pstm.close();
				FabricaDeConexao.fechaConexao();
			}
			catch (SQLException e){
				e.printStackTrace();
			}
		}

	}
	
}
